﻿using System;
using System.Data.SQLite;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace DCT.CodeBox.BMS.Code.Testing
{
    public class TestDataAccess
    {
        private static string dataConnString = @"Data Source=..\..\..\data\sample_data.s3db";

        public void Execute1() {
            string insertSql = "INSERT INTO [{0}] "
                + "([NAME],[REMARK],[ISADMIN],[STATUS],[CREATE_DATETIME]) "
                + "VALUES (@name, @remark, @isadmin, @status, @createtime)";
            //string insertPermission = "INSERT INTO [{0}] "
            //    + "([ROLE_ID,[PERMISSION_NAME],[CREATE_DATETIME]])"
            //    + "VALUES (@roleid, @pname, @createtime)";


            using (SQLiteConnection conn = new SQLiteConnection(dataConnString))
            {
                conn.Open();
                SQLiteCommand cmd = conn.CreateCommand();
                cmd.CommandText = string.Format(insertSql, "ROLE_INFO");

                {
                    SQLiteParameter p = new SQLiteParameter("name");
                    p.DbType = System.Data.DbType.String;
                    p.Size = 50;
                    p.Value = "mytest";
                    cmd.Parameters.Add(p);
                }
                {
                    SQLiteParameter p = new SQLiteParameter("remark");
                    p.DbType = System.Data.DbType.String;
                    p.Size = 100;
                    p.Value = "myremark";
                    cmd.Parameters.Add(p);
                }
                {
                    SQLiteParameter p = new SQLiteParameter("isadmin");
                    p.DbType = System.Data.DbType.Boolean;
                    p.Value = false;
                    cmd.Parameters.Add(p);
                }
                {
                    SQLiteParameter p = new SQLiteParameter("status");
                    p.DbType = System.Data.DbType.Int32;
                    p.Value = 2;
                    cmd.Parameters.Add(p);
                }
                {
                    SQLiteParameter p = new SQLiteParameter("createtime");
                    p.DbType = System.Data.DbType.VarNumeric;                    
                    p.Value = GetTimeTick(DateTime.Now);
                    cmd.Parameters.Add(p);
                }

                int effectRow = cmd.ExecuteNonQuery();
                Console.WriteLine("result=" + effectRow.ToString());
            }
        }

        public void Execute2()
        {
            string insertSql = "INSERT INTO [{0}] "
                + "([NAME],[REMARK],[ISADMIN],[STATUS],[CREATE_DATETIME]) "
                + "VALUES (@name, @remark, @isadmin, @status, @createtime)";

            using (SQLiteConnection conn = new SQLiteConnection(dataConnString))
            {
                
                SQLiteCommand cmd = conn.CreateCommand();
                cmd.CommandText = string.Format(insertSql, "ROLE_INFO");

                {
                    SQLiteParameter p = new SQLiteParameter("name");
                    p.DbType = System.Data.DbType.String;
                    p.Size = 50;
                    p.Value = "mytest" + DateTime.Now.Ticks.ToString();
                    cmd.Parameters.Add(p);
                }
                {
                    SQLiteParameter p = new SQLiteParameter("remark");
                    p.DbType = System.Data.DbType.String;
                    p.Size = 100;
                    p.Value = "myremark";
                    cmd.Parameters.Add(p);
                }
                {
                    SQLiteParameter p = new SQLiteParameter("isadmin");
                    p.DbType = System.Data.DbType.Boolean;
                    p.Value = false;
                    cmd.Parameters.Add(p);
                }
                {
                    SQLiteParameter p = new SQLiteParameter("status");
                    p.DbType = System.Data.DbType.Int32;
                    p.Value = 2;
                    cmd.Parameters.Add(p);
                }
                {
                    SQLiteParameter p = new SQLiteParameter("createtime");
                    p.DbType = System.Data.DbType.VarNumeric;
                    p.Value = GetTimeTick(DateTime.Now);
                    cmd.Parameters.Add(p);
                }

                conn.Open();

                using (var trans = conn.BeginTransaction()) {


                    cmd.Transaction = trans;

                    int effectRow = cmd.ExecuteNonQuery();
                    Console.WriteLine("result=" + effectRow.ToString());

                    string sql = "Select LAST_INSERT_ROWID()";

                    object result = new SQLiteCommand(sql, conn, trans).ExecuteScalar();

                    Console.WriteLine("lastid=" + result.ToString());

                    trans.Rollback();
                }              
            }
        }

        private static DateTime DEFAULT_MIN_DATETIME = new DateTime(1900, 1, 1);

        private static double GetTimeTick(DateTime time) {
            TimeSpan ts = (TimeSpan)(time - DEFAULT_MIN_DATETIME);
            return ts.TotalMilliseconds;
        }
    }
}
